﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Xml;
using System.Net;
using Microsoft.Crm.Sdk.Discovery.Proxy;
using Microsoft.Crm;

//included to clean up registries when we are loading the entitymanager database
using Microsoft.Win32;

namespace CRM_Perf_BenchMark
{
	public class EntityNotFoundException : System.Exception
	{
		public EntityNotFoundException(string Message):base(Message)
		{
		}
	}

    public static class Roles
    {
        public const string SalesPerson = "Salesperson";
        public const string SystemAdministrator = "System Administrator";
        public const string MarketingProfessional = "Marketing Professional";
        public const string CSR = "Customer Service Representative";
        public const string Scheduler = "Scheduler";
        public const string VPMarketing = "Vice President of Marketing";
        public const string SalesManager = "Sales Manager";
        public const string ScheduleManager = "Schedule Manager";
        public const string CEO = "CEO-Business Manager";
        public const string SupportUser = "Support User";
        public const string MarketingManager = "Marketing Manager";
        public const string SystemCustomizer = "System Customizer";
        public const string CSRManager = "CSR Manager";
        public const string VPSales = "Vice President of Sales";        
    }

    /// <summary>
    /// List of enty names. The naming scheme corresponds to the CRM 2.0 database table naming scheme.
    /// </summary>
    public static class EntityNames
    {
        public const string Users = "SystemUser";
        public const string Leads = "Lead";
        public const string Opportunities = "Opportunity";
        public const string Accounts = "Account";
        public const string Appointments = "Appointment";
        public const string Emails = "Email";
        public const string Phonecalls = "Phonecall";
        public const string Letters = "Letter";
        public const string Faxes = "Fax";
        public const string Contacts = "Contact";
        public const string GenericActivities = "GenericActivities";
        public const string Service = "Service";
        public const string Subject = "Subject";
        public const string Tasks = "Task";
        public const string Quotes = "Quote";
        public const string Templates = "Template";
        public const string Roles = "Role";
        public const string Incidents = "Incident";
        public const string Notes = "Annotation";
        public const string PriceLevels = "PriceLevel";
        public const string Lists = "List";
        public const string BusinessUnits = "BusinessUnit";
        public const string Campaigns = "Campaign";
		public const string ServiceAppointments = "ServiceAppointment";
        public const string Privileges = "Privilege";
        public const string Organizations = "Organization";
        public const string Equipment = "Equipment";
        public const string Reports = "Report";
        public const string New_Simple_CustomAccounts = "New_Simple_CustomAccount";
        public const string TransactionCurrency = "TransactionCurrency";
    }

    /// <summary>
    /// Names of the primary key for each entity type.
    /// </summary>
    public static class EntityIDNames
    {
        public const string User = "SystemUserId";
        public const string Lead = "LeadId";
        public const string Opportunities = "OpportunityId";
        public const string Account = "AccountId";
        public const string Appointment = "ActivityId";
        public const string Phonecall = "ActivityId";
        public const string Fax = "ActivityId";
        public const string Email = "ActivityId";
        public const string Letter = "ActivityId";
        public const string Contact = "ContactId";
        public const string Task = "ActivityId"; 
        public const string Service = "ServiceId";
        public const string Subject = "SubjectId";
        public const string Quote = "QuoteId";
        public const string Template = "TemplateId";
        public const string Role = "RoleId";
        public const string Incident = "IncidentId";
        public const string Note = "AnnotationId";
        public const string PriceLevel = "PriceLevelId";
        public const string List = "ListId";
        public const string BusinessUnit = "BusinessUnitId";
        public const string Campaign = "CampaignId";
		public const string ServiceAppointment = "ActivityId";
        public const string Privilege = "Name";
        public const string Organization = "OrganizationId";
        public const string Equipment = "EquipmentId";
        public const string Report = "ReportId";        
        public const string TransactionCurrency = "TransactionCurrencyId";
    }

	public enum EntityStates
	{
		Free = 0,
		InUse = 1,
		Deleted = 2
	}

    /// <summary>
    /// Static(ish) Class to manage CRM Entities for perf testing.
    /// </summary>
	public class EntityManager
	{
		//singleton instance
		private static EntityManager m_instance = null;
        private static object OwnerLock = new object();

        public const string EntityManagerID = "ID";
		public const string EntityManagerStateCode = "State";
        public const string EntityManagerOutlookStateCode = "OutlookState";

        //we are changing the BitShift from 46 to 25 to support multi org scenarios
        //we are assuming we are not going to have more than 1000 orgs => 10 bits
        //Remaining bits = 64- 10 = 54. We do shift twice - one for Org owned entities
        //and another for user owned entities

        //In future we are going to have different BitShift value for Org owned entities and
        //user owned entities and these will be read from ConfigSettings.xml so that
        //we will be able to change them run time

        //with Bit shift == 25, each user can have 2^25 = 33,554,432 entities
		private const int BitShift = 25;

		/// <summary>
        /// Connection to SQL Server, used for Init()
        /// </summary>
        private SqlConnection m_CRMSQLCon = null;
        private SqlConnection m_EMSQLCon = null;
        private SqlConnection m_CONFIGDBCon = null;

		private System.Random m_RanNum = new Random();

        private static System.Collections.Generic.Dictionary<Guid,Hashtable> m_Owners = new System.Collections.Generic.Dictionary<Guid,Hashtable>();

        //we are moving to multiple CRM servers
        //we will read these per crm server
        //server name
        //sql conn
        //user passowrd
        //user base
        //user count

        string m_OrgName;
        string m_CRMServer;
        string m_discoveryServer;
        string m_APIServer;
        string m_userBase;
        string m_userStart;
        string m_userCount;
        string m_sqlConn;
        string m_userPassword;

        string m_configsqlConn;
        
        Hashtable m_outlookUserStart;
        Hashtable m_outlookUserCount;
        Hashtable m_outlookUserBase;

        string m_CRMDom;

		static long SearchMisses = 0;

		#region Orgization ID
		/// <summary>
		/// Makes life easier to just expose the ORG's GUID.
        /// we are using this in case of multi org scenario too - we set this value
        /// for every org we read to read entities for that org
		/// </summary>
		private Guid m_OrgID = Guid.Empty;
        private string m_TokenKey = null;
        private int m_numOrgs = 0;

        private ArrayList m_OrganizationCount = new ArrayList();
        private ArrayList m_OrganizationIds = new ArrayList();
        private int m_TotalUsers = 0;

        //to suport multiple Orgs, we will assume a fake parent org
        public Guid m_FakeOrgID = Guid.Empty;
		public Guid OrgId
		{
			get
			{
                //here we going to get a random Org from the list of orgs
				return GetRandomOrg();
			}
		}
        /// <summary>
        /// This function returns a random org
        /// </summary>
        public Guid GetRandomOrg()
        {
            if (m_instance == null)
                m_instance = new EntityManager();
            if (m_numOrgs == 1 && m_OrgID != Guid.Empty)
                return m_OrgID;
            //here we are going to look at the organization, get a count and get a random org                    

            if (m_OrganizationCount.Count == 0)
            {
                SqlConnection SqlCon = new SqlConnection(m_EMSQLCon.ConnectionString);
                SqlDataReader reader;
                try
                {
                    SqlCon.Open();

                    //Build the SQL for the current request
                    SqlTransaction tran = SqlCon.BeginTransaction(System.Data.IsolationLevel.Serializable);
                                        
                    m_TotalUsers = 0;

                    SqlCommand cmd = new SqlCommand();
                    cmd.Transaction = tran;
                    cmd.Connection = SqlCon;
                    cmd.CommandText = "SELECT organizationid, count(*) as count from systemuser group by organizationid";
                    
                    reader = cmd.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            m_TotalUsers = m_TotalUsers + int.Parse(reader["count"].ToString());
                            m_OrganizationCount.Add(m_TotalUsers);
                            string currentOrg = reader["organizationid"].ToString();
                            m_OrganizationIds.Add(currentOrg);
                        }
                    }
                    finally
                    {
                        if (!reader.IsClosed)
                        {
                            reader.Close();
                        }
                        tran.Commit();
                    }
                }
                finally
                {
                    if (SqlCon != null)
                    {
                        SqlCon.Close();
                    }
                }
            }
            double RanNum = m_RanNum.NextDouble();
            int i = 0;
            while (RanNum > ((double)((int)m_OrganizationCount[i]) / (double)m_TotalUsers))
            {
                i++;
            }
            string orgId = (string)m_OrganizationIds[i];
            System.Diagnostics.Trace.WriteLine("Random Org " + i + " chosen. ID is :" + orgId);
            return new Guid(orgId);
        }

        public Guid GetOwnerGuid(Guid user, Guid Org)
        {
            //Owner.Add("CrmServer", m_CRMServer);
            Byte[] b1 = user.ToByteArray();
            Byte[] b2 = Org.ToByteArray();
            Byte[] b3 = new byte[16];

            for (int i = 0; i < 16; i++)
                b3[i] = (byte)((int)b1[i] + (int)b2[i]);

            Guid g = new Guid(b3);
            return g;

        }

        public void CleanOutlookSyncRegistry()
        {
            string origKeyPath = "SOFTWARE\\Microsoft";
            //we are going to copy all the registry from this and then replace values for userId etc

            if (Registry.CurrentUser.OpenSubKey(origKeyPath) != null)
            {

                RegistryKey regKey = Registry.CurrentUser.OpenSubKey(origKeyPath);


                string[] valueNames = regKey.GetSubKeyNames();

                for (int i = 0; i < valueNames.Length; i++)
                {
                    //we need to see if name has MSCRMClient in it then we need to delete the key
                    if (valueNames[i].ToLower().IndexOf("mscrm") == 0)
                        Registry.CurrentUser.DeleteSubKey(origKeyPath + "\\" +  valueNames[i]);

                }
            }
        }

        public void CreateConfigDBRegistry(string configdbValue)
        {
            string origKeyPath = "SOFTWARE\\Microsoft\\MSCRM";
            //we are going to copy all the registry from this and then replace values for userId etc

            RegistryKey mscrm = Registry.LocalMachine.CreateSubKey(origKeyPath);
            mscrm.SetValue("configdb", configdbValue, RegistryValueKind.String);
            mscrm.SetValue("ConfigSku", ConfigSettings.Default.ConfigSku, RegistryValueKind.String);
        }
		#endregion

		#region Admin User
		/// <summary>
		/// Quick reference pointer to admin user
		/// </summary>
		private CRMEntity m_AdminUser = null;
        /// <summary>
        /// Accessor for the admin user in the CRM deployment. Handy since some user owned entities are
        /// really pseudo org owned and parked with this user.
        /// </summary>
        public CRMEntity AdminUser
        {
            get
            {
                return m_instance.m_AdminUser;
            }
		}
		#endregion

        /// <summary>
        /// Constructor of static instance. Emtpy.
        /// </summary>
		static EntityManager()
		{
			m_instance = new EntityManager();
			//CrmSDK.InitMDCache();
		}

		private EntityManager ()
		{
			Init();
		}

        public void entManTrc( string tstNm, string usrNm )
        {
            System.Diagnostics.Trace.WriteLine("[Thread], " +
            System.Threading.Thread.CurrentThread.GetHashCode().ToString() +
            ", [Is Running Test], " + tstNm +
            ", [As User], " + usrNm );
        }
                
        /// <summary>
        /// Switch to determine if a requested entity is a user owned or ORG owned entity.
        /// </summary>
        /// <param name="EntityName">EntityName for entity Get it from ManagersLibrary.EntityNames</param>
        /// <returns>True if entity is user owned, false otherwise.</returns>
        public static bool IsUserEntity(string EntityName)
        {
            switch (EntityName)
            {
                case EntityNames.Accounts:
                case EntityNames.Contacts:
                case EntityNames.Leads:
                case EntityNames.Opportunities:
                case EntityNames.Tasks:
                case EntityNames.Templates:
                case EntityNames.Quotes:
                case EntityNames.Incidents:
                case EntityNames.Notes:
                case EntityNames.Lists:
                case EntityNames.Campaigns:
				case EntityNames.ServiceAppointments:                
                    return true;
                default:
                    return false;
            }
        }

        /// <summary>
        /// Accessor for instance of EntityManager. Also will init manager if it has not been done yet.
        /// </summary>
		public static EntityManager Instance
		{
			get
			{
                return m_instance;
			}
		}

        /// <summary>
        /// Initializes manager by reading app settings from app.config.
        /// Then loads information about all entities from the CRM SQL Server.
        /// It is up to callers to provide thread protection, this function is not multi-thread friendly.
        /// </summary>
		private void Init()
		{
            RegControl regControl = new RegControl();
            regControl.SetContext(ApplicationContext.ServerContext, true);
            regControl.SetContext(ApplicationContext.ClientContext, false);
			//load the memory structures from the DB
			LoadFromSQL();
		}
        
		public static string GetEntityIDName(string Type)
		{
			switch (Type)
			{
				case EntityNames.Accounts:
					return EntityIDNames.Account;
				case EntityNames.BusinessUnits:
					return EntityIDNames.BusinessUnit;
				case EntityNames.Campaigns:
					return EntityIDNames.Campaign;
				case EntityNames.Contacts:
					return EntityIDNames.Contact;
                case EntityNames.Equipment:
                    return EntityIDNames.Equipment;
				case EntityNames.Incidents:
					return EntityIDNames.Incident;
				case EntityNames.Leads:
					return EntityIDNames.Lead;
				case EntityNames.Lists:
					return EntityIDNames.List;                
				case EntityNames.Notes:
					return EntityIDNames.Note;
				case EntityNames.Opportunities:
					return EntityIDNames.Opportunities;
				case EntityNames.Organizations:
					return EntityIDNames.Organization;
				case EntityNames.PriceLevels:
					return EntityIDNames.PriceLevel;
				case EntityNames.Privileges:
					return EntityIDNames.Privilege;
				case EntityNames.Quotes:
					return EntityIDNames.Quote;
				case EntityNames.Roles:
					return EntityIDNames.Role;
				case EntityNames.Service:
					return EntityIDNames.Service;
				case EntityNames.ServiceAppointments:
					return EntityIDNames.ServiceAppointment;
				case EntityNames.Subject:
					return EntityIDNames.Subject;
				case EntityNames.Tasks:
					return EntityIDNames.Task;
				case EntityNames.Templates:
					return EntityIDNames.Template;
				case EntityNames.Users:
					return EntityIDNames.User;
                case EntityNames.Reports:
                    return EntityIDNames.Report;
                case EntityNames.TransactionCurrency:
                    return EntityIDNames.TransactionCurrency;
				default:
					throw new NotImplementedException("Add Type " + Type.ToString());
			}
		}

		/// <summary>
		/// Truncate the SQL tables for a new set of data.
		/// </summary>
		private void TruncateTables()
        {
            SqlCommand TruncateCommand = new SqlCommand();

            TruncateCommand.Connection = m_EMSQLCon;
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Accounts;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.BusinessUnits;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Campaigns;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Contacts;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Equipment;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.GenericActivities;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Incidents;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Leads;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Lists;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Notes;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Opportunities;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Organizations;
            TruncateCommand.ExecuteNonQuery();
			TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.PriceLevels;
			TruncateCommand.ExecuteNonQuery();
			TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Privileges;
			TruncateCommand.ExecuteNonQuery();
			TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Quotes;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Reports;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Roles;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Service;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.ServiceAppointments;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Subject;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Tasks;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.TransactionCurrency;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Appointments;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Emails;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Letters;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Faxes;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Phonecalls;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Templates;
            TruncateCommand.ExecuteNonQuery();
            TruncateCommand.CommandText = "TRUNCATE TABLE " + EntityNames.Users;
            TruncateCommand.ExecuteNonQuery();
            
            TruncateCommand.CommandText = "TRUNCATE TABLE " + "FakeParentOrg";
            TruncateCommand.ExecuteNonQuery();
        }
       
		/// <summary>
        /// Loads CRM deployment information from SQL.
        /// </summary>
        private void LoadFromSQL()
        {
            // Try block to wrap SQL access to ensure connection is closed. No Exception handling.
            try
            {
                // Mark the start time for diagnostic data. Nice to know how long the load took.
                System.DateTime Start = System.DateTime.Now;

                CreateConfigDBRegistry(ConfigSettings.Default.m_MulServers[0].ConfigSqlCNN);

                bool bFound = false;

                //Get BuildVersion from CRM buildversion table
                string buildNumber = "";
                for (int i = 0; i < ConfigSettings.Default.m_MulServers.Length; i++)
                {
                    m_sqlConn = ConfigSettings.Default.m_MulServers[i].SQLCNN;
                }
                m_CRMSQLCon = new SqlConnection(m_sqlConn);
                m_CRMSQLCon.Open();
                
                Trace.WriteLine("Connected to CRM SQL Database to retrieve buildnumber");
                
                SqlCommand retrieveVersion = new SqlCommand();
                retrieveVersion.Connection = m_CRMSQLCon;
                retrieveVersion.CommandText = "select BuildNumber from buildversion";
                SqlDataReader CRMReader = retrieveVersion.ExecuteReader();
                try
                {
                    while (CRMReader.Read())
                    {
                        buildNumber = CRMReader["BuildNumber"].ToString();
                    }
                }
                finally
                {
                    CRMReader.Close();
                    m_CRMSQLCon.Close();                                       
                }

                //If you want to reload the EntityManager db, change the number in the xml config file
                //Read the ServerInfo From the EM DB.
                m_EMSQLCon = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
                m_EMSQLCon.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_EMSQLCon;
                cmd.CommandText = "SELECT * FROM SystemInfo";

                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        if (reader["ServerCN"].ToString().ToLower().Equals(buildNumber + "_" + (ConfigSettings.Default.ReloadEntityManager)))
                        {
                            //Not the most exact method, but the perf team does put the build number into the ORG.
                            bFound = true;
                            break;
                        }
                    }
                }
                finally
                {
                    reader.Close();
                    m_EMSQLCon.Close();
                }

                if (bFound == false)
                {
                    //Also we are going to create outlook sync dirs
                    if (ConfigSettings.Default.OutlookSyncDir != string.Empty)
                    {
                        Directory.CreateDirectory(ConfigSettings.Default.OutlookSyncDir + "\\SyncToOutlook");
                        Directory.CreateDirectory(ConfigSettings.Default.OutlookSyncDir + "\\ManagedABPSYnc");
                        Directory.CreateDirectory(ConfigSettings.Default.OutlookSyncDir + "\\GoOffline");

                    }
					//we have to read how many crm servers we are reading from
                    Trace.WriteLine("Connecting to EntityManager SQL: " + ConfigSettings.Default.EMSQLCNN);
                    m_EMSQLCon = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
                    m_EMSQLCon.Open();
                    Trace.WriteLine("Connected to Entity Manager SQL Database");

                    TruncateTables();

                    PopulateFakeParentOrg();
                    
                    m_outlookUserStart = new Hashtable();
                    m_outlookUserCount = new Hashtable();
                    m_outlookUserBase = new Hashtable();

                    m_CRMDom = ConfigSettings.Default.CRMDomain;

                    m_numOrgs = ConfigSettings.Default.m_MulServers.Length;

                    for (int i = 0; i < m_numOrgs; i++)
                    {
                        m_OrgName = ConfigSettings.Default.m_MulServers[i].OrgName;

                        m_sqlConn = ConfigSettings.Default.m_MulServers[i].SQLCNN;
                        m_userBase = ConfigSettings.Default.m_MulServers[i].userbase;
                        m_userCount = ConfigSettings.Default.m_MulServers[i].count;
                        m_userStart = ConfigSettings.Default.m_MulServers[i].start;
                        m_userPassword = ConfigSettings.Default.m_MulServers[i].userPassword;
                        m_CRMServer = ConfigSettings.Default.m_MulServers[i].CRMServer;
                        m_discoveryServer = ConfigSettings.Default.m_MulServers[i].DiscoveryServer;
                        m_APIServer = ConfigSettings.Default.m_MulServers[i].APIServer;

                        m_configsqlConn = ConfigSettings.Default.m_MulServers[i].ConfigSqlCNN;
                        
                        Trace.WriteLine("Connecting up to CONFIG DB");
                        m_CONFIGDBCon = new SqlConnection(m_configsqlConn);
                        m_CONFIGDBCon.Open();
                        Trace.WriteLine("Connected to CONFIG DB");

                        Trace.WriteLine("Connecting to CRM SQL: " + m_sqlConn);
                        m_CRMSQLCon = new SqlConnection(m_sqlConn);
                        m_CRMSQLCon.Open();
                        Trace.WriteLine("Connected to CRM SQL Database");

                        GetCurrentOrg();

                        if (!m_outlookUserStart.ContainsKey(m_OrgID))
                        {
                            m_outlookUserStart.Add(m_OrgID, ConfigSettings.Default.m_MulServers[i].outlookstart);
                        }
                        if (!m_outlookUserCount.ContainsKey(m_OrgID))
                        {
                            m_outlookUserCount.Add(m_OrgID, ConfigSettings.Default.m_MulServers[i].outlookcount);
                        }
                        if (!m_outlookUserBase.ContainsKey(m_OrgID))
                        {
                            m_outlookUserBase.Add(m_OrgID, ConfigSettings.Default.m_MulServers[i].userbase);
                        }

                        ReadOrgs();
                        ReadUsers();
                        ResetOutlookStateCode(EntityNames.Users);
                        //Start reading Entities.
                        ReadBUs();
                        ReadLeads();
                        ReadOpportunities();
                        ReadAccounts();
                        ReadContacts();
                        ReadAppointments();
                        ReadEmails();
                        ReadFaxes();
                        ReadLetters();
                        ReadPhonecalls();
                        ReadTasks();
                        ReadSubjects();
                        ReadServices();
                        ReadQuotes();
                        ReadRoles();
                        ReadIncidents();
                        ReadNotes();
                        ReadCampaigns();
                        ReadPriceLevels();
                        ReadLists();
                        ReadServiceAppointments();
                        ReadPrivileges();
                        ReadEquipment();
                        ReadReports();
                        ReadTransactionCurrencies();
                        ReadSystemUserRoles();                        
                        m_TokenKey = ReadTokenKey(m_OrgID.ToString());
                        m_CRMSQLCon.Close();
                        m_CONFIGDBCon.Close();

                    }

                    //Write the server info out to the EM SQL DB. 
                    //Read the ServerInfo From the EM DB.
                    m_EMSQLCon = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
                    m_EMSQLCon.Open();
                    cmd = new SqlCommand();
                    cmd.Connection = m_EMSQLCon;
                    cmd.CommandText = "INSERT INTO SystemInfo (ServerCN) VALUES('" + buildNumber + "_" + ConfigSettings.Default.ReloadEntityManager + "')";
                    if (1 != cmd.ExecuteNonQuery())
                        Trace.WriteLine("Failed to update SystemInfotable. You'll be realoading next time.");
                    
				}
				else
				{

                    Trace.WriteLine("Connecting to EntityManager SQL: " + ConfigSettings.Default.EMSQLCNN);
                    m_EMSQLCon = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
                    m_EMSQLCon.Open();
                    Trace.WriteLine("Connected to Entity Manager SQL Database");
					BuildFromSQL();
				}

                //Read the ServerInfo From the EM DB.
                cmd = new SqlCommand();
                cmd.Connection = m_EMSQLCon;
				//Get the Admin User.
				cmd.CommandText = "SELECT * FROM SYSTEMUSER WHERE SetupUser=1";

                reader = cmd.ExecuteReader();
				try
				{
                    if (false == reader.Read())
                    {
                        m_AdminUser = null;
                    }
                    else
                    {
                        m_AdminUser = new CRMEntity(reader, EntityNames.Users);
                        m_AdminUser["userpassword"] = "youradminpassword";
                    }
				}
				finally
				{
					reader.Close();
				}

                if (m_AdminUser != null)
                {
                    cmd.CommandText = "UPDATE SYSTEMUSER SET " + EntityManagerStateCode + "=" + (int)EntityStates.InUse + " WHERE SetupUser=1";
                    cmd.ExecuteNonQuery();
                }

                //Diagnostics.
                Trace.WriteLine("Elapsed Time: " + System.DateTime.Now.Subtract(Start).ToString());
            }
            catch (Exception e)
            {
                Trace.WriteLine("Exception: " + e.ToString());
            }
            finally
            {
                
            }
        }

		private void BuildFromSQL()
		{
            //to support multi server scenario, we are going to read crmServer name also from Org as well as user table
            //Read from the EM DB and setup the memory structures we need to run the test cases.
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_EMSQLCon;
            cmd.CommandText = "SELECT " + EntityIDNames.Organization + " as OwnerID," + EntityManagerID + " , CrmServer FROM " + EntityNames.Organizations;
            
            SqlDataReader reader = cmd.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    //Build the owners table.
                    Hashtable Owner = new Hashtable();

                    System.Int64 ID = System.Int64.Parse(reader[1].ToString());
                    Owner.Add(EntityManagerID, ID);
                    if (m_Owners.ContainsKey(reader.GetGuid(0)))
                        continue;

                    m_Owners.Add(reader.GetGuid(0), Owner);
                    m_OrgID = reader.GetGuid(0);
                                        
                    m_numOrgs++;
                }
            }
            finally
            {
                reader.Close();
            }

            m_outlookUserStart = new Hashtable();
            m_outlookUserCount = new Hashtable();
            m_outlookUserBase = new Hashtable();
                        
            m_CRMDom = ConfigSettings.Default.CRMDomain;

            for (int i = 0; i < m_numOrgs; i++)
            {
                try
                {   
                    m_sqlConn = ConfigSettings.Default.m_MulServers[i].SQLCNN;

                    Trace.WriteLine("Connecting to CRM SQL to get orgID: " + m_sqlConn);
                    m_CRMSQLCon = new SqlConnection(m_sqlConn);
                    m_CRMSQLCon.Open();
                    Trace.WriteLine("Connected to CRM SQL Database to get orgID");

                    GetCurrentOrg();

                    if (!m_outlookUserStart.ContainsKey(m_OrgID))
                    {
                        m_outlookUserStart.Add(m_OrgID, ConfigSettings.Default.m_MulServers[i].outlookstart);
                    }
                    if (!m_outlookUserCount.ContainsKey(m_OrgID))
                    {
                        m_outlookUserCount.Add(m_OrgID, ConfigSettings.Default.m_MulServers[i].outlookcount);
                    }
                    if (!m_outlookUserBase.ContainsKey(m_OrgID))
                    {
                        m_outlookUserBase.Add(m_OrgID, ConfigSettings.Default.m_MulServers[i].userbase);
                    }                    
                }
                catch (Exception e)
                {
                    Trace.WriteLine(e.ToString());
                }
                finally
                {
                    m_CRMSQLCon.Close();
                }
            }

            m_TokenKey = ReadTokenKey(m_OrgID.ToString());

            cmd.CommandText = "SELECT " + EntityIDNames.User + " as OwnerID ," + EntityIDNames.Organization + " as OrgID,"+ EntityManagerID + " , CrmServer FROM " + EntityNames.Users;

            

            reader = cmd.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    //Build the owners table.
                    Hashtable Owner = new Hashtable();
                    Guid g = GetOwnerGuid(reader.GetGuid(0), reader.GetGuid(1));
                    if (m_Owners.ContainsKey(g))
                        continue;
                    System.Int64 ID = System.Int64.Parse(reader[2].ToString());
                    Owner.Add(EntityManagerID, ID);

                    m_Owners.Add(g, Owner);
                }
            }
            finally
            {
                reader.Close();
            }

			BuildEntityFromSQL(EntityNames.Users, "OrganizationID");
            ResetOutlookStateCode(EntityNames.Users);
			BuildEntityFromSQL(EntityNames.Leads, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Accounts, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Contacts, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Opportunities, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Service, "OrganizationID");
			BuildEntityFromSQL(EntityNames.Subject, "OrganizationID");
			BuildEntityFromSQL(EntityNames.Tasks, "EntityManagerOwningUser");
            BuildEntityFromSQL(EntityNames.Appointments, "EntityManagerOwningUser");
            BuildEntityFromSQL(EntityNames.Emails, "EntityManagerOwningUser");
            BuildEntityFromSQL(EntityNames.Faxes, "EntityManagerOwningUser");
            BuildEntityFromSQL(EntityNames.Letters, "EntityManagerOwningUser");
            BuildEntityFromSQL(EntityNames.Phonecalls, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Quotes, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Templates, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Incidents, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Notes, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.PriceLevels, "OrganizationID");
			BuildEntityFromSQL(EntityNames.Lists, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.BusinessUnits, "OrganizationID");
			BuildEntityFromSQL(EntityNames.Campaigns, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.ServiceAppointments, "EntityManagerOwningUser");
			BuildEntityFromSQL(EntityNames.Roles, "OrganizationID");
			BuildEntityFromSQL(EntityNames.Privileges, "OrganizationID");
            BuildEntityFromSQL(EntityNames.Equipment, "OrganizationID");
            BuildEntityFromSQL(EntityNames.Reports, "OrganizationID");
            BuildEntityFromSQL(EntityNames.TransactionCurrency, "OrganizationID");            
		}

		private void ResetStateCode(string EntityName)
		{
            int rowCnt = 0;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
            cmd.Connection.Open();
            cmd.CommandText = "UPDATE " + EntityName + " SET " + EntityManagerStateCode + "=" + (int)EntityStates.Free;
            cmd.CommandText += " WHERE " + EntityManagerStateCode + "=" + (int)EntityStates.InUse;
            try
            {
                rowCnt = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.ToString());
            }
            finally
            {
                cmd.Connection.Close();
            }
		}

        private void ResetOutlookStateCode(string EntityName)
        {
            int rowCnt = 0;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
            cmd.Connection.Open();
            cmd.CommandText = "UPDATE " + EntityName + " SET " + EntityManagerOutlookStateCode + "=" + (int)EntityStates.Free;
            cmd.CommandText += " WHERE " + EntityManagerOutlookStateCode + "=" + (int)EntityStates.InUse;
            try
            {
                rowCnt = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Trace.WriteLine(e.ToString());
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

		private void BuildEntityFromSQL(string EntityName, string KeyName)
		{
			Trace.WriteLine("Building memory tables for entity: " + EntityName);

			SqlCommand cmd = new SqlCommand();
			cmd.Connection = m_EMSQLCon;
            cmd.CommandTimeout = 0;

			cmd.CommandText = "SELECT count(*) as Count, " + KeyName + " FROM " + EntityName + " GROUP BY " + KeyName;
			SqlDataReader reader = cmd.ExecuteReader();

			try
			{
				while (reader.Read())
				{
					Hashtable Owner = m_Owners[new Guid(reader[KeyName].ToString())];

					System.Int64 NumRead = System.Int64.Parse(reader["Count"].ToString());

					Owner.Add(EntityName, NumRead);
				}
			}
			finally
			{
				reader.Close();
			}

			ResetStateCode(EntityName);
		}

        /// <summary>
        /// Overloaded ReadEntityFromTable. Assumes no optional WHERE filter is needed.
        /// </summary>
        /// <param name="EntityName">Name of entity to read. This MUST correspond to the SQL table name for the entity.</param>
        /// <param name="Props">String array of properties to read, i.e. {"OwnerID", "CreatedBy"} etc.
        ///                     Prop[0] MUST be the owerID for the entity.
        ///                     Prop[1] MUST be the primary key for the entity.
        ///                     Additional properties optional.</param>
        /// <param name="UserOwned">Is the entityType owned by a ORG or a user.</param>
        /// <returns>double with count of read entities.</returns>
        private double ReadEntityFromTable(string EntityName, string[] Props)
        {
            return ReadEntityFromTable(EntityName, Props, null, null);
        }

		private double ReadEntityFromTable(string EntityName, string[] Props,string where)
		{
			return ReadEntityFromTable(EntityName, Props, where, null);
		}

        /// <summary>
        /// Primary ReadEntityFromTable.
        /// </summary>
        /// <param name="EntityName">Name of entity to read. This MUST correspond to the SQL table name for the entity.</param>
        /// <param name="Props">String array of properties to read, i.e. {"OwnerID", "CreatedBy"} etc.
        ///                     Prop[0] MUST be the owerID for the entity.
        ///                     Prop[1] MUST be the primary key for the entity.
        ///                     Additional properties optional.</param>
        /// <param name="UserOwned">Is the entityType owned by a ORG or a user.</param>
        /// <param name="WHERE">WHERE clause added to end of query to filter. "WHERE" keyword provided by caller.</param>
        /// <returns>double with count of read entities.</returns>
        private double ReadEntityFromTable(string EntityName, string[] Props, string WHERE, string strORDERBY)
        {
            //Build the SELECT statement with the requested props
            string select = "SELECT ";
            string InsertCommand = "INSERT INTO " + EntityName + " (" + EntityManagerID + ", EntityManagerOwningUser ,";

			//Counter of entities read.
			double Read = 0;

			SqlTransaction Tran = m_EMSQLCon.BeginTransaction();
			try
			{
				foreach (string Prop in Props)
				{
					if (Prop != null)
					{
						select += Prop + ",";
						InsertCommand += Prop + ",";
					}
				}
				string ORDERBY = null;

				if (null != strORDERBY)
					ORDERBY = " ORDER BY " + strORDERBY;
				else if (null != Props[0])
					ORDERBY = " ORDER BY " + Props[0];

				//Remove the last ","
				select = select.Remove(select.Length - 1);
				InsertCommand = InsertCommand.Remove(InsertCommand.Length - 1);

				InsertCommand += ") ";

				//Add WHERE clause if specified.
				select += " from " + EntityName + " " + WHERE + ORDERBY;

				//Open SQL Reder.
				SqlCommand cmd = new SqlCommand(select, m_CRMSQLCon);
				cmd.CommandTimeout = 0;
				SqlDataReader reader = cmd.ExecuteReader();

				SqlCommand EMCmd = new SqlCommand();
				EMCmd.Connection = m_EMSQLCon;
				EMCmd.Transaction = Tran;
                EMCmd.CommandTimeout = 0;

				try // Ensure readser.close() is called. No exception handling.
				{
					//Hashtable to store requested properties.
					while (reader.Read())
					{
						EMCmd.Parameters.Clear();

						Read++;
						string Values = "VALUES (";

						//First need to determine the owner's number.
                        Hashtable Owner = null;
                        Guid OwnerId = Guid.Empty;

                        
						if (null == Props[0])
						{
                            //////There is no owner so set the Int to 0.
                            ////Values += "@" + EntityManagerID + ",";
                            ////EMCmd.Parameters.AddWithValue(EntityManagerID, 0);

                            //to support multi server scenario, we are going to have a fake org
                            //it the first property is null => we are reading orgs
                            Owner = m_Owners[m_FakeOrgID];
                            OwnerId = m_FakeOrgID;

						}
						else
						{
                            //if the owner is an Organization, then we do not get the ownerid
                            //we get the new owner id if the owner is a user
                            if(Props[0] == EntityIDNames.Organization)
                                OwnerId = reader.GetGuid(0);
                            else
                                OwnerId = GetOwnerGuid(reader.GetGuid(0), m_OrgID);
							//The owner should exist.
							//Owner = m_Owners[reader.GetGuid(0)];

                            Owner = m_Owners[OwnerId];
                        }

						if (false == Owner.ContainsKey(EntityName))
							Owner.Add(EntityName, (System.Int64)0);

						System.Int64 BaseVal = System.Int64.Parse(Owner[EntityManagerID].ToString());
						BaseVal = BaseVal << BitShift;

						System.Int64 NextID = (System.Int64)Owner[EntityName];
						NextID++;
						if (System.Int64.MaxValue == NextID)
							throw new Exception("Attempt to use reserved const of Int64.MaxValue");

						Owner[EntityName] = NextID;
						NextID += BaseVal;
						Values += "@" + EntityManagerID + ",";
						EMCmd.Parameters.AddWithValue(EntityManagerID, NextID);
                        Values += "@" + "EntityManagerOwningUser ,";
                        EMCmd.Parameters.AddWithValue("EntityManagerOwningUser", OwnerId);
                    

						int i = 0;


						foreach (string Prop in Props)
						{
							if (Prop != null)
							{
								Values += "@" + Prop + ",";

								if (reader.IsDBNull(i))
									EMCmd.Parameters.AddWithValue(Prop, Guid.Empty);
								else if ("SqlBoolean" == reader.GetProviderSpecificFieldType(i).Name)
								{
									bool b = bool.Parse(reader[Prop].ToString());
									if (true == b)
										EMCmd.Parameters.AddWithValue(Prop, 1);
									else
										EMCmd.Parameters.AddWithValue(Prop, 0);
								}
								else
									EMCmd.Parameters.AddWithValue(Prop, reader[Prop].ToString());

								i++;
							}
						}
						Values = Values.Remove(Values.Length - 1);

						Values += ")";

						//Insert the row.
						EMCmd.CommandText = InsertCommand + Values;

						if (1 != EMCmd.ExecuteNonQuery())
						{
							Trace.Write("Didn't effect 1 row");
						}

						Trace.WriteLineIf(0 == (Read % 1000), "Read " + Read + " " + EntityName + " (More to Come)");
					}
				}
				catch (System.Exception e)
				{

					Trace.WriteLine("Exception:\n" + e.ToString());
					Trace.WriteLine("SQL " + EMCmd.CommandText);
					throw;
				}
				//Close the reader.
				finally
				{
					reader.Close();
				}
			}
			finally
			{
				Tran.Commit();
			}

            return Read;
        }

        public void UpdateEntity(ref CRMEntity Entity, string EntityName, Guid Owner, string[] PropNames, string[] PropValues, string EntityIdName, string EntityId)
        {
            for (int i = 0; i < PropNames.Length; i++)
            {
                Entity[PropNames[i]] = PropValues[i];
            }

            System.Int64 NextID = System.Int64.Parse("0") ;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(m_EMSQLCon.ConnectionString);
            cmd.Connection.Open();
            try
            {
                cmd.CommandText = "UPDATE " + EntityName + " set ";
                         
                // Check if the entity["owninguser"] is different from the new owner
                // if so create a new EntityManagerID for the entity row in the DB and
                // point the new owner to this new entity row through the new ID
                if (Entity["EntityManagerOwningUser"] != Owner.ToString())
                {
                    // Need the ID value.
                    //The owner should exist.
                    lock (OwnerLock)
                    {
                        Hashtable OwnersEntities = m_Owners[Owner];

                        if (false == OwnersEntities.ContainsKey(EntityName))
                            OwnersEntities.Add(EntityName, System.Int64.Parse("0"));

                        System.Int64 BaseVal = System.Int64.Parse(OwnersEntities[EntityManagerID].ToString());
                        System.Int64 BaseVal2 = BaseVal << BitShift;

                        NextID = (System.Int64)OwnersEntities[EntityName];
                        NextID++;
                        if (System.Int64.MaxValue == NextID)
                            throw new Exception("Attempt to use reserved const of Int64.MaxValue");

                        OwnersEntities[EntityName] = NextID;
                        System.Int64 NextID2 = BaseVal + NextID;
                        cmd.CommandText += "ID = '" + NextID2 + "',";
                    }
                }

                for (int i = 0; i < PropNames.Length; i++)
                {
                    cmd.CommandText += PropNames[i] + "=";
                    cmd.CommandText += "'" + PropValues[i] + "',";
                }
                cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 1);

                //cmd.CommandText += " where " + EntityIdName + "='" + EntityId + "'";

                //in mutli server system, we can have same entity ids, so we
                //are going to update on ID
                cmd.CommandText += " where " + EntityManagerID + "='" + Entity[EntityManagerID] + "'";
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                {
                    Trace.WriteLine("Reopen closed m_EMSQLCon connection");
                    cmd.Connection.Open();
                }

                cmd.ExecuteNonQuery();
                //
                // If the update succeeded then we will be in this code path
                // If the NextID is not null, it means the Entity has a new owner
                // then we need to update the EntityManagerID to the new ID.
                //
                if (NextID != System.Int64.Parse("0"))
                {
                    Entity[EntityManagerID] = NextID.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("EMDB UpdateEntity Failure: " + ex.ToString());
            }
            finally
            {
                cmd.Connection.Close();
            }
        }


//        /// <summary>
//        /// Adds a new entity to the EntityLists
//        /// </summary>
//        /// <param name="EntityName">Entity type name</param>
//        /// <param name="PropNames"></param>
//        /// <param name="PropValues"></param>
        public void AddEntity(string EntityName, Guid Owner, string[] PropNames, string[] PropValues)
        {
            System.Int64 BaseVal = System.Int64.Parse("0");
            System.Int64 NextID = System.Int64.Parse("0");
            System.Int64 BaseVal2 = System.Int64.Parse("0");
            System.Int64 NextID2 = System.Int64.Parse("0");

			SqlCommand cmd = new SqlCommand();
			cmd.Connection = new SqlConnection(m_EMSQLCon.ConnectionString);
			cmd.Connection.Open();
			try
			{
				cmd.CommandText = "INSERT INTO " + EntityName + " (ID,";

				foreach (string Prop in PropNames)
					cmd.CommandText += Prop + ",";

				cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 1);

				cmd.CommandText += ") VALUES(";

				// Need the ID value.
				//The owner should exist.
				lock (OwnerLock)
				{
					Hashtable OwnersEntities = m_Owners[Owner];

					if (false == OwnersEntities.ContainsKey(EntityName))
						OwnersEntities.Add(EntityName, System.Int64.Parse("0"));

					BaseVal = System.Int64.Parse(OwnersEntities[EntityManagerID].ToString());
					BaseVal2 = BaseVal << BitShift;

					NextID = (System.Int64)OwnersEntities[EntityName];
					NextID++;
					if (System.Int64.MaxValue == NextID)
						throw new Exception("Attempt to use reserved const of Int64.MaxValue");

					OwnersEntities[EntityName] = NextID;
					NextID2 = BaseVal2 + NextID;
					cmd.CommandText += NextID2 + ",";
				}

				foreach (string Prop in PropValues)
					 cmd.CommandText += "'" + Prop + "',";

				cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 1);

				cmd.CommandText += ")";

                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                {
                    Trace.WriteLine("Reopen closed m_EMSQLCon connection");
                    cmd.Connection.Open();
                }

				if (1 != cmd.ExecuteNonQuery())
					throw new Exception("Failed to insert new entity into table: " + cmd.CommandText);
			}
            catch (Exception ex)
            {
                throw new Exception("EMDB AddEntity Failure: " + ex.ToString());
            }
			finally
			{
				cmd.Connection.Close();
			}
		}


		#region Entity Loading
		/// <summary>
        /// Reads all defined incidents.
        /// </summary>
        private void ReadIncidents()
        {
            string[] Fields = { "OwnerID", "IncidentID", "Title" };
            string Table = EntityNames.Incidents;
            string where = " where deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(Table, Fields, where) + " Incidents");
        }

        private void ReadNotes()
        {
            string[] Fields = { "OwnerID", "AnnotationID", "ObjectID", "ObjectTypeCode" };
            string Table = EntityNames.Notes;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(Table, Fields, where) + " Notes");
        }

		private void ReadLeads()
        {
            string[] Props = { "OwningUser", EntityIDNames.Lead, "FirstName", "LastName" };
            string EntityName = EntityNames.Leads;
            string where = " where deletionstatecode = 0 and statuscode = 1 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where,"OwningUser") + " Leads");
        }
        private void ReadCampaigns()
        {
            string[] Props = { "OwningUser", EntityIDNames.Campaign };
            string EntityName = EntityNames.Campaigns;
            string where = " where deletionstatecode = 0 ";
            where = CreateQueryForUserSubset (where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Campaigns");
        }

        /// <summary>
        /// Reads all defined Quotes.
        /// </summary>
        private void ReadQuotes()
        {
            string[] Props = { "OwnerID", EntityIDNames.Quote, "Name", "CustomerIdName", "CustomerId", "CustomerIdType" };
            string EntityName = EntityNames.Quotes;
            string where = " WHERE deletionstatecode = 0 and statecode = 0 ";
            where = CreateQueryForUserSubset(where);
            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Quotes");
        }

		/// <summary>
        /// Reads all defined opportunities.
        /// </summary>
        private void ReadOpportunities()
        {
            string[] Props = { "OwningUser", EntityIDNames.Opportunities , "name"};
            string EntityName = EntityNames.Opportunities;
            string where = " where deletionstatecode = 0 and statecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Opportunities");
        }

        /// <summary>
        /// Reads all defined accounts
        /// </summary>
        private void ReadAccounts()
        {
            string[] Props = { "OwningUser", EntityIDNames.Account, "name" };
            string EntityName = EntityNames.Accounts;
            string where = " where deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Accounts");
        }

        /// <summary>
        /// Reads all contacts
        /// </summary>
        private void ReadContacts()
        {
            string[] Props = { "OwningUser", EntityIDNames.Contact, "lastname" };
            string EntityName = EntityNames.Contacts;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Contacts");
        }

        /// <summary>
        /// Reads all defined tasks
        /// </summary>
        private void ReadTasks()
        {
            string[] Props = { "OwningUser", EntityIDNames.Task };
            string EntityName = EntityNames.Tasks;
            string where = " WHERE deletionstatecode = 0 and statecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Tasks");
        }

        /// <summary>
        /// Reads all defined appointments
        /// </summary>
        private void ReadAppointments()
        {
            string[] Props = { "OwningUser", EntityIDNames.Appointment };
            string EntityName = EntityNames.Appointments;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Appointments");
        }

        /// <summary>
        /// Reads all defined emails
        /// </summary>
        private void ReadEmails()
        {
            string[] Props = { "OwningUser", EntityIDNames.Email };
            string EntityName = EntityNames.Emails;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Emails");
        }

        /// <summary>
        /// Reads all defined letters
        /// </summary>
        private void ReadLetters()
        {
            string[] Props = { "OwningUser", EntityIDNames.Letter };
            string EntityName = EntityNames.Letters;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Letters");
        }

        /// <summary>
        /// Reads all defined phonecalls
        /// </summary>
        private void ReadPhonecalls()
        {
            string[] Props = { "OwningUser", EntityIDNames.Phonecall };
            string EntityName = EntityNames.Phonecalls;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Phonecalls");
        }

        /// <summary>
        /// Reads all defined faxes
        /// </summary>
        private void ReadFaxes()
        {
            string[] Props = { "OwningUser", EntityIDNames.Fax };
            string EntityName = EntityNames.Faxes;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Faxes");
        }

        /// <summary>
        /// Reads all defined lists
        /// </summary>
        private void ReadLists()
        {
            string[] Props = { "OwningUser", EntityIDNames.List };
            string EntityName = EntityNames.Lists;
            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " Lists");
        }

        /// <summary>
        /// Reads all defined lists
        /// </summary>
        private void ReadServiceAppointments()
        {
            string[] Props = { "OwningUser", EntityIDNames.ServiceAppointment };
            string EntityName = EntityNames.ServiceAppointments;

            string where = " WHERE deletionstatecode = 0 ";
            where = CreateQueryForUserSubset(where);

            Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props, where) + " ServiceAppointments");
        }

        private void ReadOrgs()
        {
            string[] Props = { null, EntityIDNames.Organization, "TokenKey" };

            double NumOrgs = ReadEntityFromTable(EntityNames.Organizations, Props);

            if (1 != NumOrgs)
                throw new Exception(NumOrgs + " Were read from SQL. The number is not 1.");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = m_EMSQLCon;
            cmd.CommandText = "UPDATE " + EntityNames.Organizations + " SET CrmServer='" + m_CRMServer + "' where " + EntityIDNames.Organization + "='" + m_OrgID + "'";
            Trace.WriteLine("Issuing SQL Command to set default password " + cmd.CommandText);
            cmd.ExecuteNonQuery();

            //Hashtable OrgOwner = new Hashtable();

            //OrgOwner.Add(EntityManagerID, (System.Int64)0);

            //Need to enter the org as a owner in our global table.
            string SqlCommand = "SELECT  " + EntityIDNames.Organization + "," + EntityManagerID + " FROM " + EntityNames.Organizations + " where " + EntityIDNames.Organization + "='" + m_OrgID + "'";
            SqlCommand GetOrg = new SqlCommand(SqlCommand, m_EMSQLCon);
            SqlDataReader reader = GetOrg.ExecuteReader();
            try
            {
                while (reader.Read())
                {

                    //if (false == reader.Read())
                    //    throw new Exception("Failed to read any rows while searching for the org.");

                    //m_Owners.Add(reader.GetGuid(0), OrgOwner);
                    //m_OrgID = reader.GetGuid(0);

                    Hashtable Owner = new Hashtable();

                    Owner.Add(EntityManagerID, System.Int64.Parse(reader[EntityManagerID].ToString()));
                    if (m_Owners.ContainsKey(reader.GetGuid(0)))
                        continue;

                   
                    m_Owners.Add(reader.GetGuid(0), Owner);
                }
            }
            finally
            {
                reader.Close();
            }
        }

        public CrmDiscoveryService CreateDiscoveryService()
        {
            CrmDiscoveryService service = new CrmDiscoveryService();
            string targetHostName;
            if (m_CRMServer == "")
            {
                targetHostName = "http://localhost";
            }
            else
            {
                targetHostName = m_discoveryServer;
            }
            service.Url = string.Format("{0}/MSCRMServices/2007/{1}/CrmDiscoveryService.asmx", targetHostName, "Passport");

            return service;
        }

        public CrmDiscoveryService CreateDiscoveryService(string discoveryServer)
        {
            CrmDiscoveryService service = new CrmDiscoveryService();
            string targetHostName;
            if (m_CRMServer == "")
            {
                targetHostName = "http://localhost";
            }
            else
            {
                targetHostName = discoveryServer;
            }
            service.Url = string.Format("{0}/MSCRMServices/2007/{1}/CrmDiscoveryService.asmx", targetHostName, "Passport");

            return service;
        }

        private void ReadUsers()
        {
            if (m_userBase == null)
                return;
            //Need to alter the systemuser table to set our default password.

            string[] Props = { EntityIDNames.Organization, EntityIDNames.User, "DomainName", "BusinessUnitId", "SetupUser", "InternalEmailAddress" };
            string EntityName = EntityNames.Users;
            string where = "WHERE IsDisabled = 0 AND SetupUser = 1 or ";

            if (m_userBase != null)
            {
                string userNameBase = m_userBase;
                int userStart = int.Parse(m_userStart);
                for (int i = 0; i < int.Parse(m_userCount); i++)
                {
                    where = where + "domainname like '%\\" + userNameBase + (userStart + i).ToString() + "' or ";
                }
                where = where.Substring(0, where.Length - 3);
            }
            else
            {
                where = where.Substring(0, where.Length - 4);
            }

            Trace.WriteLine("Read: " + ReadEntityFromTable(EntityName, Props, where, EntityIDNames.User) + " Users");

            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = m_EMSQLCon;
                //cmd.CommandText = "UPDATE " + EntityNames.Users + " SET UserPassword='" + m_userPassword + "' where " + EntityIDNames.Organization + "='" + m_OrgID + "'";
                //Trace.WriteLine("Issuing SQL Command to set default password " + cmd.CommandText);
                //cmd.ExecuteNonQuery();
                cmd.CommandText = "UPDATE " + EntityNames.Users + " SET CrmServer='" + m_CRMServer + "', ApiServer='" + m_APIServer + "', DiscoveryServer='" + m_discoveryServer + "', UserPassword='" + m_userPassword + "', OrganizationName='" + m_OrgName + "' where " + EntityIDNames.Organization + "='" + m_OrgID + "'";
                Trace.WriteLine("Issuing SQL Command to set default crmserver, password, OrganizationName " + cmd.CommandText);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Trace.WriteLine("Update of User Exception: " + e.ToString());
            }
            //Need to add the users as owners
            //Need to enter the org as a owner in our global table.
            string SqlCommand = "SELECT " + EntityIDNames.User + "," + EntityManagerID + " FROM " + EntityNames.Users + " where " + EntityIDNames.Organization + "='" + m_OrgID + "'"; ;
            SqlCommand GetOrg = new SqlCommand(SqlCommand, m_EMSQLCon);
            SqlDataReader reader = GetOrg.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    Hashtable Owner = new Hashtable();

                    Owner.Add(EntityManagerID, System.Int64.Parse(reader[EntityManagerID].ToString()));

                    //get a new user guid from user id and org id
                    Guid g = GetOwnerGuid(reader.GetGuid(0), m_OrgID);
                    //if (m_Owners.ContainsKey(reader.GetGuid(0)))
                    //    continue;

                    //Owner.Add("CrmServer", m_CRMServer);
                    m_Owners.Add(g, Owner);

                }
            }
            finally
            {
                reader.Close();
            }
        }

		private void ReadPriceLevels()
		{
			string[] Props = { EntityIDNames.Organization, EntityIDNames.PriceLevel };
			string EntityName = EntityNames.PriceLevels;

			Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props) + " PriceLevels");
		}

		private void ReadSubjects()
		{
			string[] Props = { EntityIDNames.Organization, EntityIDNames.Subject, "Title" };
			string EntityName = EntityNames.Subject;

			Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props) + " Subjects");
		}

		private void ReadServices()
		{
			string[] Props = { EntityIDNames.Organization, EntityIDNames.Service, "Name" };
			string EntityName = EntityNames.Service;

			Trace.WriteLine("Read " + ReadEntityFromTable(EntityName, Props) + " Services");
		}

		private void ReadBUs()
		{
			string[] Fields = { EntityIDNames.Organization, EntityIDNames.BusinessUnit, "Name", "ParentBusinessUnitId" };
			string Table = EntityNames.BusinessUnits;

			Trace.WriteLine("Read " + ReadEntityFromTable(Table, Fields) + " BusinessUnits");
		}

		private void ReadRoles()
		{
            string[] Fields = { EntityIDNames.Organization, EntityIDNames.Role, "Name" };
			string Table = EntityNames.Roles;

			Trace.WriteLine("Read " + ReadEntityFromTable(Table, Fields) + " Roles");
		}        
        private void ReadEquipment()
        {
            string[] Fields = { EntityIDNames.Organization, EntityIDNames.Equipment, "Name" };
            string Table = EntityNames.Equipment;

            Trace.WriteLine("Read " + ReadEntityFromTable(Table, Fields) + " Equipment");
        }
        private void PopulateFakeParentOrg()
        {
            Guid fakeguid = new Guid("19007b7e-d9a3-da11-b5fc-001143d30bf2");
            
            string SqlCommand = "Insert into FakeParentOrg (ID,OrganizationId) Values (0, '" + fakeguid + "')";
            SqlCommand GetOrg = new SqlCommand(SqlCommand, m_EMSQLCon);
            GetOrg.ExecuteNonQuery();

            Hashtable OrgOwner = new Hashtable();

            OrgOwner.Add(EntityManagerID, (System.Int64)0);

            //Need to enter the org as a owner in our global table.
            SqlCommand = "SELECT  Top 1 " + EntityIDNames.Organization + " FROM FakeParentOrg";
            GetOrg = new SqlCommand(SqlCommand, m_EMSQLCon);
            SqlDataReader reader = GetOrg.ExecuteReader();
            try
            {
                while (reader.Read())
                {

                    //if (false == reader.Read())
                    //    throw new Exception("Failed to read any rows while searching for the org.");

                    m_Owners.Add(reader.GetGuid(0), OrgOwner);
                    m_FakeOrgID = reader.GetGuid(0);
                }


            }
            finally
            {
                reader.Close();
            }

        }

        private void GetCurrentOrg()
        {
            string SqlCommand = "SELECT " + EntityIDNames.Organization + " FROM " + EntityNames.Organizations;
            SqlCommand GetOrg = new SqlCommand(SqlCommand, m_CRMSQLCon);
            SqlDataReader reader = GetOrg.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    m_OrgID = reader.GetGuid(0);

                }
            }
            finally
            {
                reader.Close();
            }

        }
        #endregion

        private string CreateQueryForUserSubset(string where)
        {         

            if (where == null)
            {
                where = " ownerid in (select systemuserid from systemuserbase where setupuser = 1 or (isdisabled = '0' and ";
            }
            else
            {
                where = where + " and ownerid in (select systemuserid from systemuserbase where setupuser = 1 or (isdisabled = '0' and ";
            }

            SqlConnection sqlConn = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
            sqlConn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sqlConn;
            cmd.CommandText = "SELECT SystemUserId FROM SystemUser";

            SqlDataReader reader = cmd.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    where = where + "systemuserid = '" + reader["SystemUserId"] + "' or ";                        
                }
            }
            finally
            {
                reader.Close();
                sqlConn.Close();
            }

            where = where.Substring(0, where.Length - 3);
            where = where + "))";
            
            return where;
        }
        
        #region readTokenKey
        //we are reading token for a given org now
        public string ReadTokenKey(string orgId)
        {
            if (m_numOrgs == 1 && m_TokenKey != null)
                return m_TokenKey;

            SqlConnection SqlCon = new SqlConnection(m_EMSQLCon.ConnectionString);
            SqlCon.Open();

            //Build the SQL for the current request
            SqlTransaction tran = SqlCon.BeginTransaction(System.Data.IsolationLevel.Serializable);

            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = tran;
            cmd.Connection = SqlCon;

            cmd.CommandText = "SELECT tokenkey from organization where OrganizationId='" + orgId + "'";

            SqlDataReader reader = cmd.ExecuteReader();
            string tokenKey = string.Empty;

            try // Ensure readser.close() is called. No exception handling.
            {
                while (reader.Read())
                {
                    tokenKey = reader["tokenkey"].ToString();
                }
            }
            //Close the reader.
            finally
            {
                reader.Close();
                tran.Commit();
                SqlCon.Close();
            }

            //Trace.WriteLine("Read TokenKey = " + tokenKey);
            return tokenKey;
        }
        #endregion

//        #region UnownedEntities

        private void ReadReports()
        {
            //Build the SELECT statement with the requested props
            string select = "SELECT ReportId, FileName, ReportTypeCode FROM Report";
            string InsertCommand = "INSERT INTO Report (" + EntityManagerID + ", OrganizationID, EntityManagerOwningUser, ReportId, FileName, ReportTypeCode )";

            //Open SQL Reder.
            SqlCommand cmd = new SqlCommand(select, m_CRMSQLCon);
            SqlDataReader reader = cmd.ExecuteReader();

            SqlCommand EMCmd = new SqlCommand();
            EMCmd.Connection = m_EMSQLCon;

            //Counter of entities read.
            double Read = 0;

            try // Ensure readser.close() is called. No exception handling.
            {
                //Hashtable to store requested properties.
                while (reader.Read())
                {
                    Read++;
                    string Values = " VALUES (";


                    //First need to determine the owner's number.
                    //The owner should exist.
                    Hashtable Owner = m_Owners[this.m_OrgID];

                    if (false == Owner.ContainsKey(EntityNames.Reports))
                        Owner.Add(EntityNames.Reports, (System.Int64)0);

                    System.Int64 NextID = (System.Int64)Owner[EntityNames.Reports];
                    NextID++;
                    if (System.Int64.MaxValue == NextID)
                        throw new Exception("Attempt to use reserved const of Int64.MaxValue");

                    System.Int64 BaseVal = System.Int64.Parse(Owner[EntityManagerID].ToString());
                    BaseVal = BaseVal << BitShift;

                    Owner[EntityNames.Reports] = NextID;
                    NextID += BaseVal;

                    Values += NextID + ",";
                    Values += "'" + m_OrgID + "',";
                    Values += "'" + m_OrgID + "',";
                    Values += "'" + reader["ReportId"].ToString() + "',";                    
                    Values += "'" + reader["FileName"].ToString() + "',";
                    Values += "'" + reader["ReportTypeCode"].ToString() + "')";

                    //Insert the row.
                    EMCmd.CommandText = InsertCommand + Values;

                    if (1 != EMCmd.ExecuteNonQuery())
                    {
                        Trace.Write("Didn't effect 1 row");
                    }

                }
            }
            catch (System.Exception e)
            {
                Trace.WriteLine("Exception:\n" + e.ToString());
                throw;
            }
            //Close the reader.
            finally
            {
                reader.Close();
            }
            Trace.WriteLine("Read " + Read + " Reports");

        }

        private void ReadSystemUserRoles()
        {
            string select = @"select a.domainname as domainname, b.name as rolename from systemuser a, rolebase b, systemuserroles c
                                where a.systemuserid = c.systemuserid 
                                and
                                b.roleid = c.roleid";

            string insertCommand = @"update systemuser ";
            
            double Read = 0;

            try
            {
                SqlCommand cmd = new SqlCommand(select, m_CRMSQLCon);
                SqlDataReader reader = cmd.ExecuteReader();

                SqlCommand EMCmd = new SqlCommand();
                EMCmd.Connection = m_EMSQLCon;

                while (reader.Read())
                {
                    Read++;
                    string values = "set Role = '" + reader["rolename"] + "' where domainname = '" + reader["domainname"] + "'";

                    EMCmd.CommandText = insertCommand + values;

                    try
                    {
                        if (1 != EMCmd.ExecuteNonQuery())
                        {
                            Trace.Write("Didn't effect 1 row");
                        }
                    }
                    catch (Exception er)
                    {
                        Trace.WriteLine("Exception:\n" + er.ToString());
                    }
                }

                reader.Close();
                cmd.Dispose();
                EMCmd.Dispose();

            }
            catch (Exception e)
            {
                Trace.WriteLine("Exception:\n" + e.ToString());
                throw;
            }
            
            Trace.WriteLine("Read " + Read + " Systemuserroles");
        }

        private void ReadTransactionCurrencies()
        {
            //Build the SELECT statement with the requested props
            string select = "SELECT TransactionCurrencyId, CurrencyName, CurrencySymbol, ISOCurrencyCode FROM TransactionCurrency";
            string InsertCommand = "INSERT INTO TransactionCurrency (" + EntityManagerID + ", OrganizationID, EntityManagerOwningUser, TransactionCurrencyId, CurrencyName, CurrencySymbol, ISOCurrencyCode )";

            //Open SQL Reder.
            SqlCommand cmd = new SqlCommand(select, m_CRMSQLCon);
            SqlDataReader reader = cmd.ExecuteReader();

            SqlCommand EMCmd = new SqlCommand();
            EMCmd.Connection = m_EMSQLCon;

            //Counter of entities read.
            double Read = 0;

            try // Ensure readser.close() is called. No exception handling.
            {
                //Hashtable to store requested properties.
                while (reader.Read())
                {
                    Read++;
                    string Values = " VALUES (";


                    //First need to determine the owner's number.
                    //The owner should exist.
                    Hashtable Owner = m_Owners[this.m_OrgID];

                    if (false == Owner.ContainsKey(EntityNames.TransactionCurrency))
                        Owner.Add(EntityNames.TransactionCurrency, (System.Int64)0);

                    System.Int64 NextID = (System.Int64)Owner[EntityNames.TransactionCurrency];
                    NextID++;
                    if (System.Int64.MaxValue == NextID)
                        throw new Exception("Attempt to use reserved const of Int64.MaxValue");

                    System.Int64 BaseVal = System.Int64.Parse(Owner[EntityManagerID].ToString());
                    BaseVal = BaseVal << BitShift;

                    Owner[EntityNames.TransactionCurrency] = NextID;
                    NextID += BaseVal;

                    Values += NextID + ",";
                    Values += "'" + m_OrgID + "',";
                    Values += "'" + m_OrgID + "',";
                    Values += "'" + reader["TransactionCurrencyId"].ToString() + "',";
                    Values += "'" + reader["CurrencyName"].ToString() + "',";
                    Values += "'" + reader["CurrencySymbol"].ToString() + "',";
                    Values += "'" + reader["ISOCurrencyCode"].ToString() + "')";

                    //Insert the row.
                    EMCmd.CommandText = InsertCommand + Values;

                    if (1 != EMCmd.ExecuteNonQuery())
                    {
                        Trace.Write("Didn't effect 1 row");
                    }

                }
            }
            catch (System.Exception e)
            {
                Trace.WriteLine("Exception:\n" + e.ToString());
                throw;
            }
            //Close the reader.
            finally
            {
                reader.Close();
            }
            Trace.WriteLine("Read " + Read + " Currencies");

        }

        private void ReadPrivileges()
        {
			//Build the SELECT statement with the requested props
			string select = "SELECT PrivilegeId, Name FROM Privilege";
			string InsertCommand = "INSERT INTO Privilege (" + EntityManagerID + ", OrganizationID, PrivilegeID, Name )";

			//Open SQL Reder.
			SqlCommand cmd = new SqlCommand(select, m_CRMSQLCon);
			SqlDataReader reader = cmd.ExecuteReader();

			SqlCommand EMCmd = new SqlCommand();
			EMCmd.Connection = m_EMSQLCon;

			//Counter of entities read.
			double Read = 0;

			try // Ensure readser.close() is called. No exception handling.
			{
				//Hashtable to store requested properties.
				while (reader.Read())
				{
					Read++;
					string Values = " VALUES (";
					

					//First need to determine the owner's number.
					//The owner should exist.
					Hashtable Owner = m_Owners[this.m_OrgID];

					if (false == Owner.ContainsKey(EntityNames.Privileges))
						Owner.Add(EntityNames.Privileges, (System.Int64)0);

					System.Int64 NextID = (System.Int64)Owner[EntityNames.Privileges];
					NextID++;
					if (System.Int64.MaxValue == NextID)
						throw new Exception("Attempt to use reserved const of Int64.MaxValue");

					System.Int64 BaseVal = System.Int64.Parse(Owner[EntityManagerID].ToString());
					BaseVal = BaseVal << BitShift;

					Owner[EntityNames.Privileges] = NextID;
					NextID += BaseVal;

					Values += NextID + ",";
					Values += "'" + m_OrgID + "',";
					Values += "'" + reader["PrivilegeId"].ToString() + "',";
					Values += "'" + reader["Name"].ToString() + "')";

					//Insert the row.
					EMCmd.CommandText = InsertCommand + Values;

					if (1 != EMCmd.ExecuteNonQuery())
					{
						Trace.Write("Didn't effect 1 row");
					}

				}
			}
			catch (System.Exception e)
			{
				Trace.WriteLine("Exception:\n" + e.ToString());
				throw;
			}
			//Close the reader.
			finally
			{
				reader.Close();
			}
			Trace.WriteLine("Read " + Read + " Privileges");
        }

//        #endregion


        #region Fetch / Checkout


//        /// <summary>
//        /// Gets the next user not marked as busy
//        /// </summary>
//        /// <returns>CRMEntity object for the user</returns>
        public CRMEntity GetNextUser()
        {
			return GetNextUser(new Hashtable());			
        }
        public CRMEntity GetNextUser(Guid Org)
        {
            return GetNextUser(new Hashtable(), Org);
        }

        
        //we are going to lock the outlook user (to make sure this user can do only one thing at a time)
        public CRMEntity GetNextOutlookUser()
        {
            Guid orgID = GetRandomOrg();
            int outlookusercount = -1;
            int outlookuserstart = -1;
            string userBase = "";
            CRMEntity user;
            Hashtable props = new Hashtable();

            bool hasOutlookUsers = false;

            while (!hasOutlookUsers)
            {
                if (m_outlookUserCount[orgID] != null)
                {
                    outlookusercount = int.Parse((string)m_outlookUserCount[orgID]);
                    hasOutlookUsers = true;
                }
                if (m_outlookUserStart[orgID] != null)
                {
                    outlookuserstart = int.Parse((string)m_outlookUserStart[orgID]);
                    hasOutlookUsers = true;
                }
                if (m_outlookUserBase[orgID] != null)
                {
                    userBase = (string)m_outlookUserBase[orgID];
                }
                if (!hasOutlookUsers)
                {
                    System.Diagnostics.Trace.WriteLine(m_OrgID + " does not have any outlook users specified in configsettings.xml. Trying another org");
                    orgID = GetRandomOrg();
                }
            }

            props = new Hashtable();

            do
            {
                int currUserNum = m_RanNum.Next(outlookuserstart, outlookuserstart + outlookusercount - 1);
                if (!props.ContainsKey("domainname"))
                {
                    props.Add("domainname", m_CRMDom + "\\" + userBase + currUserNum.ToString());
                }
                else
                {
                    props["domainname"] = m_CRMDom + "\\" + userBase + currUserNum.ToString();
                }

                user = GetNextUser(props, orgID);
            } while (user[EntityManagerOutlookStateCode] != "0");
            
            SetUserOutlookStateCode(user, EntityStates.InUse);
            return user;
        }
        public CRMEntity GetNextOutlookUser(Guid Org)
        {
            int outlookusercount = -1;
            int outlookuserstart = -1;
            string userBase = "";
            CRMEntity user;
            Hashtable props = new Hashtable();

            outlookusercount = int.Parse((string)m_outlookUserCount[Org]);
            outlookuserstart = int.Parse((string)m_outlookUserStart[Org]);
            userBase = (string)m_outlookUserBase[Org];

            props = new Hashtable();

            do
            {
                int currUserNum = m_RanNum.Next(outlookuserstart, outlookuserstart + outlookusercount - 1);
                if (!props.ContainsKey("domainname"))
                {
                    props.Add("domainname", m_CRMDom + "\\" + userBase + currUserNum.ToString());
                }
                else
                {
                    props["domainname"] = m_CRMDom + "\\" + userBase + currUserNum.ToString();
                }

                user = GetNextUser(props, Org);
            } while (user[EntityManagerOutlookStateCode] != "0");

            SetUserOutlookStateCode(user, EntityStates.InUse);
            return user;
        }


//        /// <summary>
//        /// Gets the next free user object.
//        /// </summary>
//        /// <param name="Props">Properties for the requested user</param>
//        /// <returns>CRMEntity representing the user</returns>
        public CRMEntity GetNextUser(Hashtable Props)
        {
			EntityRequest er = new EntityRequest();
			er.ReturnAs = EntityNames.Users;
			er.Type = EntityNames.Users;
            er.ParentID = GetRandomOrg(); // m_OrgID; - this is to support numti org scenario
			er.Props = Props;

			Hashtable User = GetEntities(er);

			return (CRMEntity)User[er.ReturnAs];
        }

		public CRMEntity GetNextORGEntity(string EntityType)
		{
			EntityRequest er = new EntityRequest();
			er.Type = EntityType;
            er.ParentID = GetRandomOrg(); // m_OrgID; - this is to support numti org scenario
			er.ReturnAs = EntityType;
			
			return (CRMEntity)(GetEntities(er)[EntityType]);
		}

        //get a user from this org
        public CRMEntity GetNextUser(Hashtable Props, Guid Org)
        {
            EntityRequest er = new EntityRequest();
            er.ReturnAs = EntityNames.Users;
            er.Type = EntityNames.Users;
            er.ParentID = Org;
            er.Props = Props;

            Hashtable User = GetEntities(er);

            return (CRMEntity)User[er.ReturnAs];
        }

        //get an entity from this org
        public CRMEntity GetNextORGEntity(string EntityType, Guid Org)
        {
            EntityRequest er = new EntityRequest();
            er.Type = EntityType;
            er.ParentID = Org;
            er.ReturnAs = EntityType;

            return (CRMEntity)(GetEntities(er)[EntityType]);
        }

		public Hashtable GetEntities(EntityRequest er)
		{
			System.Int64 BaseVal = System.Int64.MinValue;
			System.Int64 MaxVal = System.Int64.MinValue;
			System.Int64 StartVal = System.Int64.MinValue;
			System.Int64 CurrentVal = 0;

			CRMEntity FoundEntity = null;
			Hashtable RetEntities = new Hashtable();

			SqlConnection SqlCon = new SqlConnection(m_EMSQLCon.ConnectionString);			
			
			try
			{
                SqlCon.Open();

                Guid ParentID = Guid.Empty;
				//If a parent is specified, get a start and max value.
				if (Guid.Empty != er.ParentID)
				{
                    
                    if (Guid.Empty != er.GrandParentID)
                    {
                        //this is a user owned entity, grand parent is the Org
                        ParentID = GetOwnerGuid(er.ParentID, er.GrandParentID);

                    }
                    else
                        ParentID = er.ParentID;
                    System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo();
					nfi.NumberGroupSeparator = "";

					if (false == m_Owners.ContainsKey(ParentID))
						throw new EntityNotFoundException("Failed to find owner : " + er.ParentID.ToString());
					if(false == m_Owners[ParentID].ContainsKey(er.Type))
						throw new EntityNotFoundException("Owner : " + er.ParentID.ToString() + " Does not own any " + er.Type);

					BaseVal = System.Int64.Parse(m_Owners[ParentID][EntityManagerID].ToString());
					BaseVal = BaseVal << BitShift;

					//Now get the max val we should look at.
					MaxVal = System.Int64.Parse(m_Owners[ParentID][er.Type].ToString());
					MaxVal += BaseVal;

					StartVal = System.Int64.Parse(m_Owners[ParentID][er.Type].ToString());
					Double RanNum = m_RanNum.NextDouble();
					Double dbl = RanNum * StartVal;
					StartVal = System.Int64.Parse(dbl.ToString("n0", nfi));

					StartVal += BaseVal;
				}

				CurrentVal = StartVal;
				do
				{
					//Build the SQL for the current request
					SqlTransaction tran = SqlCon.BeginTransaction(System.Data.IsolationLevel.Serializable);

					SqlCommand cmd = new SqlCommand();
					cmd.Transaction = tran;
					cmd.Connection = SqlCon;
					cmd.CommandText = "SELECT TOP 1 * FROM " + er.Type;
					cmd.CommandText += " WITH (UPDLOCK) ";
					cmd.CommandText += " WHERE " + EntityManagerStateCode + "=@" + EntityManagerStateCode + " AND ";
					cmd.Parameters.AddWithValue(EntityManagerStateCode, (int)EntityStates.Free);

					if ((System.Int64.MinValue != StartVal) ||
						(0 != er.Props.Count))
					{
						if (System.Int64.MinValue != CurrentVal)
						{
							cmd.CommandText += EntityManagerID + "=@" + EntityManagerID + " AND ";
							cmd.Parameters.AddWithValue(EntityManagerID,CurrentVal.ToString());
						}
						foreach (string Key in new ArrayList(er.Props.Keys))
						{
							object Prop = er.Props[Key];

							if (!Prop.GetType().Name.Equals("EntityRequest[]"))
							{
                                if (Key.ToLower().Equals("name") || Key.ToLower().Equals("lastname"))
                                {
                                    cmd.CommandText += Key + " like '%" + ((string)Prop) + "%' AND ";
                                }
                                else
                                {
    								cmd.CommandText += Key + "=@" + Key + " AND ";
	    							cmd.Parameters.AddWithValue(Key,Prop.ToString());
                                }
							}
                            
						}	
					}

					//Trim off the the " AND " at the end.
					cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 5);
					
					SqlDataReader reader = cmd.ExecuteReader();
					cmd.Parameters.Clear();

					try
					{
						//Lock this guy and commit the transaction.
						if (true == reader.Read())
						{
							FoundEntity = new CRMEntity(reader, er.Type);
                            //we are locking the entity only when it is not a user
                            if (er.Type != EntityNames.Users)
                            {
                                cmd.CommandText = "UPDATE " + er.Type + " SET " + EntityManagerStateCode + "=" + (int)EntityStates.InUse;
                                cmd.CommandText += " WHERE " + EntityManagerID + "=@" + EntityManagerID;
                                cmd.Parameters.AddWithValue(EntityManagerID, reader[EntityManagerID]);

                                reader.Close();

                                if (2 != cmd.ExecuteNonQuery())
                                    throw new Exception("Failed to lock row with command: " + cmd.CommandText);
                                cmd.Parameters.Clear();
                            }
						}
					}
					finally
					{
						if (!reader.IsClosed)
							reader.Close();
						
						tran.Commit();
					}

					if (null != FoundEntity)
					{
						try
						{
							//Now we need to check and see if recursion is necessary.
							foreach (string Key in new ArrayList(er.Props.Keys))
							{
								object Prop = er.Props[Key];
								if (Prop.GetType().Name.Equals("EntityRequest[]"))
								{
									EntityRequest[] EntitesRequested = (EntityRequest[])Prop;
									foreach (EntityRequest SubEr in EntitesRequested)
									{
										if (SubEr.ParentID != Guid.Empty)
											SubEr.ParentID = new Guid(FoundEntity[GetEntityIDName(er.Type)]);
										else
										{
											//Modify the requested SubEr to use a strange foreign key relationship.
											if (true == SubEr.Props.ContainsKey(Key))
												SubEr.Props.Remove(Key);

											SubEr.Props.Add(Key, FoundEntity[GetEntityIDName(er.Type)]);
												
										}
                                        SubEr.GrandParentID = ParentID;
										Hashtable NewRet = GetEntities(SubEr);

										foreach (string SubKey in new ArrayList(NewRet.Keys))
											RetEntities.Add(SubKey, NewRet[SubKey]);
									}
								}
							}
						}
						catch (EntityNotFoundException)
						{
							System.Threading.Interlocked.Increment(ref SearchMisses);

							Trace.WriteLineIf(0 == (System.Threading.Interlocked.Read(ref SearchMisses) % 1000),
									"Missed: " + System.Threading.Interlocked.Read(ref SearchMisses));

							//Trace.WriteLine("Failed to find entity: " + enf.ToString());

							//This one didn't work
							foreach (CRMEntity EntityToFree in new ArrayList(RetEntities.Values))
								FreeEntity(EntityToFree);

							RetEntities.Clear();
							FreeEntity(FoundEntity);
							FoundEntity = null;
						}
					}

					if (null == FoundEntity)
					{
						//Advance to the next position.
						if (CurrentVal++ == MaxVal)
							CurrentVal = BaseVal;
					}
					else
					{
						RetEntities.Add(er.ReturnAs, FoundEntity);
						break;
					}

				} while (StartVal != CurrentVal);

				if (null == FoundEntity)
					throw new EntityNotFoundException("Failed to find any entities for Request: " + er.ToString());
			}
			finally
			{
				SqlCon.Close();
			}

			return RetEntities;
		}

	

		#endregion

		#region Free / Delete
		public void FreeEntity(CRMEntity Entity)
        {
			SetEntityStateCode(Entity, EntityStates.Free);
		}

		public void DeleteEntity(CRMEntity Entity)
		{
			SetEntityStateCode(Entity, EntityStates.Deleted);
		}

        public void InUseEntity(CRMEntity Entity)
        {
            SetEntityStateCode(Entity, EntityStates.InUse);
        }
		private void SetEntityStateCode(CRMEntity Entity, EntityStates State)
		{
            int rowCnt = 0;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
            cmd.Connection.Open();
            cmd.CommandText = "UPDATE " + Entity["type"] + " SET " + EntityManagerStateCode + "=" + (int)State;
            cmd.CommandText += " WHERE " + EntityManagerID + "=" + Entity[EntityManagerID];

            try
            {
                rowCnt = cmd.ExecuteNonQuery();

                if (rowCnt != 2)
                {
                    Trace.WriteLine("Row Count Error: " + rowCnt);
                    throw new Exception("ExecuteNonQuery Wrong # of rows " + cmd.CommandText);
                }
            }
            catch (SqlException e)
            {
                Trace.WriteLine(e.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
		}


        public void FreeOutlookUser(CRMEntity Entity)
        {
            SetUserOutlookStateCode(Entity, EntityStates.Free);
        }
        private void SetUserOutlookStateCode(CRMEntity Entity, EntityStates State)
        {
            int rowCnt = 0;

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(ConfigSettings.Default.EMSQLCNN);
            cmd.Connection.Open();
            cmd.CommandText = "UPDATE " + Entity["type"] + " SET " + EntityManagerOutlookStateCode + "=" + (int)State;
            cmd.CommandText += " WHERE " + EntityManagerID + "=" + Entity[EntityManagerID];

            try
            {
                rowCnt = cmd.ExecuteNonQuery();

                if (rowCnt != 2)
                {
                    Trace.WriteLine("Row Count Error: " + rowCnt);
                    throw new Exception("ExecuteNonQuery Wrong # of rows " + cmd.CommandText);
                }
            }
            catch (SqlException e)
            {
                Trace.WriteLine(e.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
		#endregion

        public void DumpUsersToFile()
        {}
	}
}
